package stest1;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;


import guopangzi.pangziguo;
import stest.stest;

public class stest3 {
	public List<stest> queryAll(){
		String sql="select+*from users";
		Connection connection=pangziguo.getConnection();
		List<stest> list=new ArrayList<>();
		try {
			PreparedStatement psmt=connection.prepareStatement(sql);
			ResultSet rs=psmt.executeQuery();
			while(rs.next()){
				stest user=new stest(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),LocalDateTime.now());
				list.add(user);
			}
		}catch (SQLException e) {
			System.out.println(stest3.class.getName()+":query all method error,message is"+e.getMessage());
			
		}
		return list;
	}
	public stest queryById(int id) {
		String sql="select*from user where id=?";
		Connection connection=pangziguo.getConnection();
		stest user=null;
		try {
			PreparedStatement psmt=connection.prepareStatement(sql);
			psmt.setInt(1, id);
			ResultSet rs=psmt.executeQuery();
			if(rs.next()) {
				user=new stest(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),LocalDateTime.now());
			}
		}
			catch (SQLException e) {
				System.out.println(stest3.class.getName()+": query By Id method error,message is "+e.getMessage());
			}
			return user;
		}
		public int addUser(stest user) {
			int rowNum=0;
			String sql="insert into users(id,name,password,sex) values(users_id.nextval,?,?,?)";
			Connection connection=pangziguo.getConnection();
			try {
				PreparedStatement psmt=connection.prepareStatement(sql);
				psmt.setString(1, user.getName());
				psmt.setString(2, user.getPassWord());
				psmt.setString(3, user.getSex());
				rowNum=psmt.executeUpdate();
			} catch (SQLException e) {
				System.out.println(stest3.class.getName()+": add user method error,message is "+e.getMessage());
			}
			return rowNum;
		}
	

}
